package com.example.yan.excel_split.controller;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

import static cn.hutool.core.util.NumberUtil.add;

public class test6 {


    // 解析excel，按终端号对数据进行分组，然后对分组数据进行合计，最后将数据合并之后导出excel
    public static void main(String[] args) {
        // 把excel解析成key/value的形式
        ExcelReader reader = ExcelUtil.getReader("C:\\Users\\75763\\Desktop\\SHOP.105110000000005.20211013.02.success(1).xls");
      //  ExcelReader reader = ExcelUtil.getReader("E:\\\\桌面\\\\SHOP.105110000000005.20211013.02.success(1).xls");
        List<List<Object>> list = reader.read();

        String header = null;
        for (List<Object> objects : list) {
            // 迭代删除空单元格
            Iterator<Object> it = objects.iterator();
            while (it.hasNext()) {
                Object cellValue = it.next();
                if (cellValue == null || StringUtils.isBlank(cellValue.toString())) {
                    it.remove();
                }
            }
//            System.out.println("行数据" + JSON.toJSONString(objects));


            String merchantCode = JSON.toJSONString(objects);

            if (merchantCode.contains("商户编号") && merchantCode.contains("商户地址")) {
                System.out.println("merchantCode==>" + merchantCode);
                //把所有的中括号取消
                String str = merchantCode.substring(merchantCode.indexOf("[") + 1, merchantCode.lastIndexOf("]"));
                String st = "商户/部门编号:" + str.substring(12, 26);
                header = st;
            }

        }


        // 我们想要解析出来的表头
        String[] keyArr = new String[]{"终端号", "发卡行", "卡种", "序列号", "交易日期", "交易时间", "交易类型", "授权号", "交易金额", "小费", "分期期数", "银行手续费", "DCC返还手续费", "划账金额", "凭证号", "批次号", "POS交易序号",
                "结算账号", "订单号", "柜台编号", "系统参考号", "持卡人姓名", "付款凭证号", "备注1", "备注2"};
        JSONArray dataList = new JSONArray();
        for (List<Object> aLineList : list) {
            if (aLineList.size() < keyArr.length)// 数据不满足表头长度的不要，说明该行数据不符合我们想要的格式
                continue;
            Map<String, Object> hashMap = new HashMap<>();
            for (int j = 0; j < keyArr.length; j++) {
                if (j < aLineList.size()) {
                    Object property = aLineList.get(j);
                    hashMap.put(keyArr[j], property);
                }
            }
            dataList.add(hashMap);
        }
        System.out.println("解析之后所有符合条件的数据列表===" + dataList);
        // 终端编号的数组，最终得到一个去重的编号组

        List<String> noList = new ArrayList<>();
        for (Object objects : dataList) {
            JSONObject jsonObject = JSON.parseObject(objects.toString());
            String no = jsonObject.getString("终端号");
            if (StringUtils.isNotBlank(no)) {
                // 去除表头，只要终端编号
                if (!no.equals("终端号")) {
                    noList.add(no);
                }
            }
        }
        double totalNew = 0;
        double totalNewB = 0;
        double totalNewC = 0;
        BigDecimal totaA = new BigDecimal("0");
        BigDecimal totaB = new BigDecimal("0");
        BigDecimal totaC = new BigDecimal("0");
        BigDecimal total1 = new BigDecimal("0");
        for (Object objects1 : dataList) {
            Map jsonObject = JSON.parseObject(objects1.toString());

            String jylx = jsonObject.get("交易类型").toString();
            switch (jylx) {

                case "消费":
                    String amount = jsonObject.get("交易金额").toString();
                    totaA = total1.add(new BigDecimal(amount));
                    System.out.println("totaA==>" + totaA);
                    totalNew += Double.parseDouble(amount);
                    totaA = new BigDecimal(totalNew);
                    break;
                case "银联二维码消费(被扫)":
                    String amount1 = jsonObject.get("交易金额").toString();
                    totalNewB += Double.parseDouble(amount1);
                    totaB = new BigDecimal(totalNewB);
                    break;
                case "外卡消费":
                    String amount2 = jsonObject.get("交易金额").toString();
                    totalNewC += Double.parseDouble(amount2);
                    totaC = new BigDecimal(totalNewC);
                    break;
            }


        }

        System.out.println("-----" + totalNew);

        Integer i = 1;
        // 编号去重
        if (noList.size() > 0) {
            List<String> newNoList = new ArrayList<>(new TreeSet<>(noList));
            if (newNoList.size() > 0) {
                System.out.println("去重后的编号列表===" + newNoList);
                List<Map> allList = new ArrayList<>();
                List<Map> allListB = new ArrayList<>();
                List<Map> allListC = new ArrayList<>();
                // 要把原来的数据根据编号进行分组过滤

                List<Map> objList1 = new ArrayList<>();


                List<Map> objListA = new ArrayList<>();
                for (String no : newNoList) {
                    List<Map> objList = new ArrayList<>();
                    List<Map> objListB = new ArrayList<>();
                    List<Map> objListC = new ArrayList<>();
                    // 合计值
                    BigDecimal total = new BigDecimal("0");
                    BigDecimal totalB = new BigDecimal("0");
                    BigDecimal totalC = new BigDecimal("0");
                    // 序号
                    int num = 0;
                    int num1 = 0;
                    for (Object objects : dataList) {
                        //   Map jsonObject = JSON.parseObject(objects.toString());
                        Map jsonObject = JSON.parseObject(objects.toString());

                        Map row1 = new LinkedHashMap();
                        Map row2 = new LinkedHashMap();
                        Map row3 = new LinkedHashMap();
                        //    Map<String, Object> row1 = new LinkedHashMap<>();
//                        Map<String, Object> row11 = new LinkedHashMap<>();
//                        Map<String, Object> row2 = new LinkedHashMap<>();
//                        Map<String, Object> row3 = new LinkedHashMap<>();

                        String terNumber = jsonObject.get("终端号").toString();
                        String isBank = jsonObject.get("发卡行").toString();
                        String tranAmout = jsonObject.get("交易金额").toString();
                        String tradeType = jsonObject.get("交易类型").toString();
                        String carsds = jsonObject.get("卡种").toString();
                        String cardNumber = jsonObject.get("序列号").toString();
                        String tradeDate = jsonObject.get("交易日期").toString();
                        String tradingHour = jsonObject.get("交易时间").toString();
                        String authCode = jsonObject.get("授权号").toString();
                        String tip = jsonObject.get("小费").toString();
                        String inPeriods = jsonObject.get("分期期数").toString();
                        String bankCharges = jsonObject.get("银行手续费").toString();
                        String refundComm = jsonObject.get("DCC返还手续费").toString();
                        String arrivalAmout = jsonObject.get("划账金额").toString();
                        String voucherNumber = jsonObject.get("凭证号").toString();
                        String bathNumber = jsonObject.get("批次号").toString();
                        String tranNumber = jsonObject.get("POS交易序号").toString();
                        String settAccount = jsonObject.get("结算账号").toString();
                        String orderNumber = jsonObject.get("订单号").toString();
                        String counterNumber = jsonObject.get("柜台编号").toString();
                        String systemNumber = jsonObject.get("系统参考号").toString();
                        String cardName = jsonObject.get("持卡人姓名").toString();
                        String PayMent = jsonObject.get("付款凭证号").toString();


                        if (terNumber.equals(no)) {

                            String amount = jsonObject.get("交易金额").toString();
                            switch (tradeType) {
                                case "消费":
                                    num++;
                                    if (!terNumber.contains("终端号")) {
                                        row1.put("序号", num);
                                        row1.put("终端号", terNumber);
                                        row1.put("发卡行", isBank);
                                        row1.put("交易金额", tranAmout);
                                        row1.put("交易类型", tradeType);
                                        row1.put("卡种", carsds);
                                        row1.put("卡号-序列号", cardNumber);
                                        row1.put("交易日期", tradeDate);
                                        row1.put("交易时间", tradingHour);
                                        row1.put("授权号", authCode);
                                        row1.put("小费", tip);
                                        row1.put("分期期数", inPeriods);
                                        row1.put("银行手续费", bankCharges);
                                        row1.put("DCC返还手续费", refundComm);
                                        row1.put("划账金额", arrivalAmout);
                                        row1.put("凭证号", voucherNumber);
                                        row1.put("批次号", bathNumber);
                                        row1.put("POS交易序号", tranNumber);
                                        row1.put("结算账号", settAccount);
                                        row1.put("订单号", orderNumber);
                                        row1.put("柜台编号", counterNumber);
                                        row1.put("系统参考号", systemNumber);
                                        row1.put("持卡人姓名", cardName);
                                        row1.put("付款凭证号", PayMent);

                                    }
                                    total = total.add(new BigDecimal(amount));
                                    objList.add(row1);
                                    break;
                                case "银联二维码消费(被扫)":
                                    //row2.put("序号", num);
                                    if (!terNumber.contains("终端号")) {
                                        row2.put("序号", num);
                                        row2.put("终端号", terNumber);
                                        row2.put("发卡行", isBank);
                                        row2.put("交易金额", tranAmout);
                                        row2.put("交易类型", tradeType);
                                        row2.put("卡种", carsds);
                                        row2.put("卡号-序列号", cardNumber);
                                        row2.put("交易日期", tradeDate);
                                        row2.put("交易时间", tradingHour);
                                        row2.put("授权号", authCode);
                                        row2.put("小费", tip);
                                        row2.put("分期期数", inPeriods);
                                        row2.put("银行手续费", bankCharges);
                                        row2.put("DCC返还手续费", refundComm);
                                        row2.put("划账金额", arrivalAmout);
                                        row2.put("凭证号", voucherNumber);
                                        row2.put("批次号", bathNumber);
                                        row2.put("POS交易序号", tranNumber);
                                        row2.put("结算账号", settAccount);
                                        row2.put("订单号", orderNumber);
                                        row2.put("柜台编号", counterNumber);
                                        row2.put("系统参考号", systemNumber);
                                        row2.put("持卡人姓名", cardName);
                                        row2.put("付款凭证号", PayMent);
                                    }
                                    totalB = totalB.add(new BigDecimal(amount));
                                    objListB.add(row2);
                                    break;
                                case "外卡消费":
                                    num1++;
                                  //  row3.put("序号", num);
                                    if (!terNumber.contains("终端号")) {
                                        row3.put("序号", num1);
                                        row3.put("终端号", terNumber);
                                        row3.put("发卡行", isBank);
                                        row3.put("交易金额", tranAmout);
                                        row3.put("交易类型", tradeType);
                                        row3.put("卡种", carsds);
                                        row3.put("卡号-序列号", cardNumber);
                                        row3.put("交易日期", tradeDate);
                                        row3.put("交易时间", tradingHour);
                                        row3.put("授权号", authCode);
                                        row3.put("小费", tip);
                                        row3.put("分期期数", inPeriods);
                                        row3.put("银行手续费", bankCharges);
                                        row3.put("DCC返还手续费", refundComm);
                                        row3.put("划账金额", arrivalAmout);
                                        row3.put("凭证号", voucherNumber);
                                        row3.put("批次号", bathNumber);
                                        row3.put("POS交易序号", tranNumber);
                                        row3.put("结算账号", settAccount);
                                        row3.put("订单号", orderNumber);
                                        row3.put("柜台编号", counterNumber);
                                        row3.put("系统参考号", systemNumber);
                                        row3.put("持卡人姓名", cardName);
                                        row3.put("付款凭证号", PayMent);
                                    }
                                    totalC = totalC.add(new BigDecimal(amount));
                                    objListC.add(row3);
                                    break;
                            }

                            //     objList.add(row1);
                            // 计算合计

                            // 序号
                            i++;

                        }


                    }

                    if (objList.size() > 0) {
                        Map totalMap = new HashMap<>();
                        totalMap.put("序号", "合计");
                        totalMap.put("终端号", "");
                        totalMap.put("发卡行", "");
                        totalMap.put("交易金额", total);
                        objList.add(totalMap);
                        allList.addAll(objList);
                    }


                    if (objListB.size() > 0) {
                        Map totalMap = new HashMap<>();
                        totalMap.put("序号", "合计");
                        totalMap.put("终端号", "");
                        totalMap.put("发卡行", "");
                        totalMap.put("交易金额", totalB);
                        objListB.add(totalMap);
                        allListB.addAll(objListB);

                    }

                    if (objListC.size() > 0) {
                        Map totalMap = new HashMap<>();
                        totalMap.put("序号", "合计");
                        totalMap.put("终端号", "");
                        totalMap.put("发卡行", "");
                        totalMap.put("交易金额", totalC);
                        objListC.add(totalMap);
                        allListC.addAll(objListC);
                    }
                }

                if (allList.size() > 0) {
                    Map totalMap = new HashMap<>();
                    totalMap.put("序号", "总计合约");
                    totalMap.put("终端号", "");
                    totalMap.put("发卡行", "");
                    totalMap.put("交易金额", totaA);
                    objListA.add(totalMap);
                    allList.addAll(objListA);
                    System.out.println("根据编号分组之后的数据" + allList);
                    // 导出
                    exportExcel(allList, "A数据", header);
                }
                if (allListB.size() > 0) {
                    Map totalMap = new HashMap<>();
                    totalMap.put("序号", "总计合约");
                    totalMap.put("终端号", "");
                    totalMap.put("发卡行", "");
                    totalMap.put("交易金额", totaB);
                    allListB.add(totalMap);
                    allList.addAll(allListB);
                    System.out.println("根据编号分组之后的数据" + allList);
                    // 导出
                    exportExcel(allListB, "B数据", header);
                }
                if (allListC.size() > 0) {
                    Map totalMap = new HashMap<>();
                    totalMap.put("序号", "总计合约");
                    totalMap.put("终端号", "");
                    totalMap.put("发卡行", "");
                    totalMap.put("交易金额", totaC);
                    allListC.add(totalMap);
                    allList.addAll(allListC);
                    // 导出
                    exportExcel(allListC, "C数据", header);
                }

            }
        }
    }

    private static void exportExcel(List<Map> list, String fileName, String header) {
        // 数据写入到excel中
       /* ExcelWriter writer = ExcelUtil.getWriter("E:\\个人\\test\\" + fileName + ".xls");
        writer.write(list);
        writer.close();*/
        Date date = new Date();

        ExcelWriter writer = ExcelUtil.getWriter("E:\\桌面\\新建文件夹(4)\\" + exportDate(date) + fileName + ".xlsx");

        // ArrayList<Object> rows4 = CollUtil.newArrayList(list);
// 默认的，未添加alias的属性也会写出，如果想只写出加了别名的字段，可以调用此方法排除之
        writer.setOnlyAlias(true);
// 合并单元格后的标题行，使用默认标题样式
        writer.merge(3, header);
// 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);
        // 关闭writer，释放内存
        writer.close();
        System.out.println("执行完了");

    }


    //获取当下时间
    private static String exportDate(Date date) {

        SimpleDateFormat sdf = new SimpleDateFormat();// 格式化时间
        //   sdf.applyPattern("yyyy-MM-dd HH:mm:ss a");// a为am/pm的标记
        sdf.applyPattern("yyyyMMdd");
        //  Date date = new Date();// 获取当前时间
        System.out.println("现在时间：" + sdf.format(date)); //

        return sdf.format(date);

    }
}













